package dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class CreateTables {

	/**
	 * @param args
	 */
	public Boolean CreateTablesMethod(String path) {
		
		try {
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver");

			Connection conn = DriverManager.getConnection("jdbc:derby:"+path+";create=true");

			Statement command = conn.createStatement();

			// Bird table
			command.executeUpdate(
					"CREATE TABLE Bird (scientific_name VARCHAR(45) NOT NULL, "+
					"size INT,"+
					"risk_of_extinction VARCHAR(45) NOT NULL,"+
					"weight INT,"+
					"general_information VARCHAR(500), "+
					"map VARCHAR(100) NOT NULL,"+
					"PRIMARY KEY (scientific_name))");

			// Food table
			command.executeUpdate(
					"CREATE TABLE Food (type_of_food VARCHAR(100) NOT NULL, "+
					"Bird_scientific_name VARCHAR(45) NOT NULL, "+
					"PRIMARY KEY (type_of_food), FOREIGN KEY (Bird_scientific_name) "+
					"REFERENCES Bird (scientific_name ) "+
					"ON DELETE NO ACTION " +
					"ON UPDATE NO ACTION)");

			// Habitat table
			command.executeUpdate(
					"CREATE TABLE Habitat (precipitation VARCHAR(45), "+
					"summer_temperature_avarage INT, "+
					"winter_temperature_avarage INT, "+
					"habitat_name VARCHAR(45) NOT NULL, "+
					"PRIMARY KEY (habitat_name) )");

			// Bird_has_Habitat table
			command.executeUpdate(
					"CREATE TABLE Bird_has_Habitat (Bird_scientific_name VARCHAR(45) NOT NULL, "+
					"Habitat_habitat_name VARCHAR(45) NOT NULL, "+
					"PRIMARY KEY (Bird_scientific_name, Habitat_habitat_name), "+
					"FOREIGN KEY (Bird_scientific_name ) "+
					"REFERENCES Bird (scientific_name ) "+
					"ON DELETE NO ACTION "+
					"ON UPDATE NO ACTION, "+
					"FOREIGN KEY (Habitat_habitat_name) "+
					"REFERENCES Habitat (habitat_name) "+
					"ON DELETE NO ACTION "+
					"ON UPDATE NO ACTION)");

			// Pictures table
			command.executeUpdate(
					"CREATE TABLE Pictures (idPicture INT NOT NULL, "+
					"Bird_scientific_name VARCHAR(45) NOT NULL, "+
					"date DATE, "+
					"PRIMARY KEY (idPicture), "+
					"FOREIGN KEY (Bird_scientific_name) "+
					"REFERENCES Bird (scientific_name) "+
					"ON DELETE NO ACTION "+
					"ON UPDATE NO ACTION)");

			// Popular_Names table
			command.executeUpdate(
					"CREATE TABLE Popular_Names (name VARCHAR(100) NOT NULL, "+
					"Bird_scientific_name VARCHAR(45) NOT NULL, "+
					"PRIMARY KEY (name, Bird_scientific_name), "+
					"FOREIGN KEY (Bird_scientific_name) "+
					"REFERENCES Bird (scientific_name) "+
					"ON DELETE NO ACTION "+
					"ON UPDATE NO ACTION)");

			// Schematic_Model table
			command.executeUpdate(
					"CREATE TABLE Schematic_Model (wings VARCHAR(45) NOT NULL, "+
					"head VARCHAR(45) NOT NULL, "+
					"tail VARCHAR(45) NOT NULL, "+
					"body VARCHAR(45) NOT NULL, "+
					"crest VARCHAR(45) NOT NULL, "+
					"Bird_scientific_name VARCHAR(45) NOT NULL, "+
					"PRIMARY KEY (Bird_scientific_name), "+
					"FOREIGN KEY (Bird_scientific_name) "+
					"REFERENCES Bird (scientific_name) "+
					"ON DELETE NO ACTION "+
					"ON UPDATE NO ACTION)");

			// Worker table
			command.executeUpdate(
					"CREATE TABLE Worker (Worker_id VARCHAR(45) NOT NULL, "+
					"password VARCHAR(13) NOT NULL, "+
					"name VARCHAR(45) NOT NULL, "+
					"CPF INT NOT NULL, "+
					"birth_date TIMESTAMP, "+
					"terminal_serial_identification INT NOT NULL, "+
					"PRIMARY KEY (Worker_id))");

			// State table
			command.executeUpdate(
					"CREATE TABLE State (state_name INT NOT NULL, PRIMARY KEY (state_name))");

			// State_has_Habitat table
			command.executeUpdate(
					"CREATE TABLE State_has_Habitat (State_state_name INT NOT NULL, "+
					"Habitat_habitat_name VARCHAR(45) NOT NULL, "+
					"PRIMARY KEY (State_state_name, Habitat_habitat_name), "+
					"FOREIGN KEY (State_state_name) "+
					"REFERENCES State (state_name) "+
					"ON DELETE NO ACTION "+
					"ON UPDATE NO ACTION, "+
					"FOREIGN KEY (Habitat_habitat_name) "+
					"REFERENCES Habitat (habitat_name ) "+
					"ON DELETE NO ACTION "+
					"ON UPDATE NO ACTION)");

			command.close();
			conn.close();
		}
		catch (SQLException err){
			System.out.println("Can't create database: " + err.getMessage());
			return false;

		}
		catch(ClassNotFoundException errclass){
			System.out.println("Missing class! Check your CLASSPATH");
			return false;
		}

		System.out.println("Tables successfully created.");
		return true;
	}
	
	
}
